package com.yuehuanghun.admin.generator.model.generator;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import com.github.pagehelper.Page;
import com.yuehuanghun.admin.generator.mapper.GenTableMapper;
import com.yuehuanghun.admin.generator.model.Column;
import com.yuehuanghun.admin.generator.model.Index;
import com.yuehuanghun.admin.generator.model.Table;
import com.yuehuanghun.admin.generator.util.DatabaseDataTypesUtils;
import com.yuehuanghun.framework.util.StringUtils;

import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public abstract class AbstractTableGenerator implements TableGenerator {
    @Autowired
    protected GenTableMapper tableMapper;
    
    protected JdbcTemplate jdbcTemplate;
    
    protected DataSource dataSource;
    
    @Setter
    @Getter
    protected String catalog;

    @Setter
    @Getter
    protected String schema;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    protected Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    protected void closeConnection(Connection conn) {
        try {
            if(conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Table> getSimpleTableInfo(String tableName, String tableComment,
            Date createTimeBegin, Date createTimeEnd, int pageNum, int pageSize) {
        List<String> doneTableNames = tableMapper.findByCriteria(p -> p.selects("tableName"), String.class); //已经入gen_table表的表名
        
        Connection conn = null;
        List<Table> tableList = new ArrayList<>();
        try {
            conn = dataSource.getConnection();
            ResultSet rs = conn.getMetaData().getTables(getCatalog(), getSchema(), null, filterTableTypes());
            while (rs.next()) {
                String realTableName = rs.getString("TABLE_NAME");
                String remarks = rs.getString("REMARKS");
                String realTableNameLower = realTableName.toLowerCase();
                if(realTableNameLower.startsWith("qrtz_") || realTableNameLower.startsWith("gen_") || doneTableNames.contains(realTableName)) {
                    continue;
                }
                if(StringUtils.isNotBlank(tableName) && !realTableNameLower.contains(tableName.toLowerCase())) {
                    continue;
                }
                
                if(StringUtils.isNotBlank(tableComment) && (remarks == null || !remarks.contains(tableComment.toLowerCase()))) {
                    continue;
                }

                Table table = new Table();
                table.setJdbcName(realTableName);
                table.setTableComments(remarks);
                tableList.add(table);
            }
            
            int offset = (pageNum - 1) * pageSize;
            int offsetEnd = pageNum * pageSize;
            if(offset > tableList.size()) {
                return new Page<>();
            }
            if(offsetEnd > tableList.size()) {
                offsetEnd = tableList.size();
            }
            Page<Table> page = new Page<>();
            page.setTotal(tableList.size());
            page.addAll(tableList.subList(offset, offsetEnd));
            return page;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeConnection(conn);
        }
    }

    @Override
    public List<Table> genAllTables() {      
        Connection conn = null;
        try {
            conn = getConnection();
            DatabaseMetaData dbMetaData = conn.getMetaData();
            ResultSet tableRs = dbMetaData.getTables(getCatalog(), getSchema(), null, filterTableTypes());
            List<Table> tables = new ArrayList<>();
            while (tableRs.next()) {
                ResultSet indexRs = getIndexResultSet(dbMetaData, tableRs.getString("TABLE_NAME"));
                List<Index> indexList = createIndexList(indexRs);
                Table table = createTable(dbMetaData, tableRs, indexList);
                tables.add(table);
            }
            tableRs.close();
            return tables;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeConnection(conn);
        }
    }
    
    protected ResultSet getIndexResultSet(DatabaseMetaData dbMetaData, String tableName) throws SQLException {
    	return dbMetaData.getIndexInfo(getCatalog(), getSchema(), tableName, false, true);
    }
    
    /** 需要过滤的表类型，某些数据库会有多种类型 */
    protected String[] filterTableTypes() {
    	return new String[] {"TABLE", "SYNONYM", "VIEW"};
    }

    @Override
    public Table genTable(String tableName) {
        Table table = null;
        try {
            table = _getTable(tableName);
            if (table == null && !tableName.equals(tableName.toUpperCase())) {
                table = _getTable(tableName.toUpperCase());
            }
            if (table == null && !tableName.equals(tableName.toLowerCase())) {
                table = _getTable(tableName.toLowerCase());
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        if (table == null) {
            throw new RuntimeException(String.format("无此表名：%s", tableName));
        }
        return table;
    }

    protected Table _getTable(String tableName) throws SQLException {
        if (StringUtils.isBlank(tableName))
            throw new IllegalArgumentException("表名不能为空");
        String catalog = StringUtils.defaultIfEmpty(getCatalog(), null);
        String schema = StringUtils.defaultIfEmpty(getSchema(), null);

        Connection conn = getConnection();
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            ResultSet tableRs = dbMetaData.getTables(catalog, schema, tableName, filterTableTypes());
            ResultSet indexRs = getIndexResultSet(dbMetaData, tableName);

            if(tableRs.next()) {
            	List<Index> indexList = createIndexList(indexRs);
                Table table = createTable(dbMetaData, tableRs, indexList);
                return table;
            }
        } finally {
            closeConnection(conn);
        }
        return null;
    }

    private Table createTable(DatabaseMetaData dbMetaData, ResultSet tableRs, List<Index> indexList) throws SQLException {
        String realTableName = null;
        try {
            realTableName = tableRs.getString("TABLE_NAME");
            String remarks = tableRs.getString("REMARKS");

            Table table = new Table();
            table.setJdbcName(realTableName);
            table.setRemarks(remarks);
            table.setIndexs(indexList);
            retriveTableColumns(table, dbMetaData, indexList);

            return table;
        } catch (SQLException e) {
            throw new RuntimeException("创建 table对象异步，tableName:" + realTableName, e);
        }
    }

    protected List<Index> createIndexList(ResultSet indexRs) throws SQLException {
        List<Index> list = new ArrayList<Index>();
        Map<String, Index> map = new HashMap<String, Index>();
        while (indexRs.next()) {
            String indexName = indexRs.getString("INDEX_NAME");
            if(StringUtils.isBlank(indexName)) {
                continue;
            }
            Index index = map.get(indexName);
            if (index == null) {
                index = new Index();
                index.setIndexName(indexName);
                index.setTableName(indexRs.getString("TABLE_NAME"));
                index.setUnique(!indexRs.getBoolean("NON_UNIQUE"));

                map.put(indexName, index);

                list.add(index);
            }
            index.getColumnNames().add(indexRs.getString("COLUMN_NAME"));
        }
        return list;
    }

    protected void retriveTableColumns(Table table, DatabaseMetaData dbMetaData, List<Index> indexList) throws SQLException {

        List<String> primaryKeys = getTablePrimaryKeys(table);
        table.setPrimaryKeyColumns(primaryKeys);

        // 索引
        List<String> indices = indexList.stream().map(Index::getIndexName).collect(Collectors.toList());
        // 唯一索引
        Map<String, String> uniqueIndices = new HashMap<>();
        indexList.forEach(index -> {
        	index.getColumnNames().forEach(columnName -> uniqueIndices.put(columnName, index.getIndexName()));
        });
        // 唯一索引的列
        Map<String, List<String>> uniqueColumns = indexList.stream().filter(item -> item.isUnique()).collect(Collectors.toMap(Index::getIndexName, Index::getColumnNames));

        List<Column> columns = getTableColumns(table, primaryKeys, indices, uniqueIndices, uniqueColumns);

        for (Iterator<Column> i = columns.iterator(); i.hasNext();) {
            Column column = (Column) i.next();
            table.addColumn(column);
        }

        if (primaryKeys.size() == 0) {
            log.warn(String.format("警告: 表%s没有主键 ",table.getJdbcName()));
        }
    }

    private List<Column> getTableColumns(Table table, List<String> primaryKeys, List<String> indices,
            Map<String, String> uniqueIndices, Map<String, List<String>> uniqueColumns) throws SQLException {

        List<Column> columns = new LinkedList<>();
        ResultSet columnRs = getColumnsResultSet(table);

        while (columnRs.next()) {
            int sqlType = columnRs.getInt("DATA_TYPE");
            String sqlTypeName = columnRs.getString("TYPE_NAME");
            String columnName = columnRs.getString("COLUMN_NAME");
            String columnDefaultValue = columnRs.getString("COLUMN_DEF");

            String remarks = columnRs.getString("REMARKS");

            String autoIncrement = "NO";
            try {
                autoIncrement = columnRs.getString("IS_AUTOINCREMENT");
            } catch (Exception e) {
                log.debug("获取是否自增异常，部分数据库可忽略此异常", e);
            }

            boolean isNullable = (DatabaseMetaData.columnNullable == columnRs.getInt("NULLABLE"));
            int size = columnRs.getInt("COLUMN_SIZE");
            int decimalDigits = columnRs.getInt("DECIMAL_DIGITS");

            boolean isPk = primaryKeys.contains(columnName);
            boolean isIndexed = indices.contains(columnName);
            String uniqueIndex = (String) uniqueIndices.get(columnName);
            List<String> columnsInUniqueIndex = null;
            if (uniqueIndex != null) {
                columnsInUniqueIndex = (List<String>) uniqueColumns.get(uniqueIndex);
            }

            boolean isUnique = columnsInUniqueIndex != null && columnsInUniqueIndex.size() == 1;

            Column column = new Column(table, sqlType, sqlTypeName, columnName, size, decimalDigits, isPk, isNullable,
                    isIndexed, isUnique, columnDefaultValue, remarks);
            column.setAutoIncrement("YES".equalsIgnoreCase(autoIncrement));
            
            String columnNameLower = columnName.toLowerCase();
            if("NUMBER".equalsIgnoreCase(sqlTypeName) && ("id".equals(columnNameLower) || columnNameLower.endsWith("_id"))) { //针对ORACLE无整型的处理
                column.setJavaType("Long");
            } else {
                column.setJavaType(DatabaseDataTypesUtils.getPreferredJavaType(sqlType, size, decimalDigits));
            }
            
            columns.add(column);
        }
        columnRs.close();
        return columns;
    }

    private ResultSet getColumnsResultSet(Table table) throws SQLException {
        ResultSet columnRs = null;
        Connection connection = getConnection();
        if (table.getOwnerSynonymName() != null) {
            columnRs = connection.getMetaData().getColumns(getCatalog(), table.getOwnerSynonymName(), table.getJdbcName(), null);
        } else {
            columnRs = connection.getMetaData().getColumns(getCatalog(), getSchema(), table.getJdbcName(), null);
        }
        closeConnection(connection);
        return columnRs;
    }

    private List<String> getTablePrimaryKeys(Table table) throws SQLException {
        List<String> primaryKeys = new LinkedList<>();
        ResultSet primaryKeyRs = null;
        Connection connection = getConnection();
        if (table.getOwnerSynonymName() != null) {
            primaryKeyRs = connection.getMetaData().getPrimaryKeys(getCatalog(), table.getOwnerSynonymName(), table.getJdbcName());
        } else {
            primaryKeyRs = connection.getMetaData().getPrimaryKeys(getCatalog(), getSchema(), table.getJdbcName());
        }
        while (primaryKeyRs.next()) {
            String columnName = primaryKeyRs.getString("COLUMN_NAME");
            primaryKeys.add(columnName);
        }
        primaryKeyRs.close();
        closeConnection(connection);
        return primaryKeys;
    }
    
}
